import pymysql

class mysql:
    sql = ''
    def __init__(self, sql):
        self.name = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='typing', charset='utf8')
        self.sql = sql

    def query_all(self):
        db = self.name
        sql = self.sql
        cursor = db.cursor()
        try:
            cursor.execute(sql)
            data = cursor.fetchall()
            return data
        except:
            print('数据库操作失败')
            db.rollback()
        db.close()

    def username_query_exist(self):
        db = self.name
        sql = self.sql
        cursor = db.cursor()
        try:
            cursor.execute(sql)
            data = cursor.fetchone()
            if data[0] == 1:
                return 'exist'
            else:
                return 'not exist'
        except:
            db.rollback()
        db.close()

    def username_query_password(self):
        db = self.name
        sql = self.sql
        cursor = db.cursor()
        try:
            cursor.execute(sql)
            data = cursor.fetchone()
            return data[0]
        except:
            print('数据库操作失败')
            db.rollback()
        db.close()

    def insert_username_password(self):
        db = self.name
        sql = self.sql
        cursor = db.cursor()
        try:
            cursor.execute(sql)
            db.commit()
            return 'success'
        except:
            print('数据库操作失败')
            db.rollback()
        db.close()

    def insert_data(self):
        db = self.name
        sql = self.sql
        cursor = db.cursor()
        try:
            cursor.execute(sql)
            db.commit()
            return 'success'
        except pymysql.Error as e:
            print(e.args[0], e.args[1])
            print('数据库操作失败')
            db.rollback()
        db.close()

    def delete_table_data(self):
        db = self.name
        sql = self.sql
        cursor = db.cursor()
        try:
            cursor.execute(sql)
            db.commit()
            return 'success'
        except pymysql.Error as e:
            print(e.args[0], e.args[1])
            print('数据库操作失败')
            db.rollback()
        db.close()

    def updata_data(self):
        db = self.name
        sql = self.sql
        cursor = db.cursor()
        try:
            cursor.execute(sql)
            db.commit()
            return 'success'
        except pymysql.Error as e:
            print(e.args[0], e.args[1])
            print('数据库操作失败')
            db.rollback()
        db.close()

username = '2003003135'
password = '321'
score = '10'
true_text = '5'
error_text = '5'
text_time = '10'
over_time = '10'
text_name = 'a'

a1 = mysql(sql='select * from text')
# b = a1.query_all()
# print(b)

a2 = mysql(sql="select exists(select * from text where text_name = '%s')" % (text_name))
# exist = a2.username_query_exist()
# print(exist)

a3 = mysql(sql="select text_content from text where text_name = '%s'" % (username))
# print(a3.username_query_password())

a4 = mysql(sql="insert into users(username,password)values('%s','%s')" % (username, password))
# print(a4.insert_username_password())

# a5 = mysql(sql="insert into score(username,score,true_text,error_text,text_time)values('%s','%s','%s','%s','%s')" % (username,score,true_text,error_text,text_time ))
# print(a5.insert_true_error_time())

a5 = mysql(sql="insert into text_data(username,over_time,text_name)values('%s','%s','%s')" % (username, over_time, text_name))
# print(a5.insert_data())
# name = 'e'
# a6 = mysql(sql="delete from text where text_name = '%s'" % (name))
# print(a6.delete_table_data())

a7 = mysql(sql='select * from score order by true_text desc limit 10')
b = a7.query_all()
print(b)

name = []
text = []

for i in b:
    print(i)
    name.append(i[0])
    text.append(i[2])

print(name)
print(text)

a8 = mysql(sql="update text_data set over_time = '%s',text_name='%s' where username = '%s'" %(over_time,text_name,username))
# a8.updata_data()

a9 = mysql(sql="select true_text from score where username ='%s'  order by true_text" %(username))
# print(a9.query_all())
name = 'Nasty place'
content = "Then the time come for you to be your own man and take on the world and you did.But somewhere along the line you changed.You stopped being you .You let people stick a finger in your face and tell you you are no good.And when things got hard ,you started looking for something to blame like a big shadow.Let me tell you something you already know.The world ain't all sunshine and rainbows.It' s a very mean and nasty place ,and I don' t care how tough you are.It will beat you to your knees and keep you there permanently if you let it."
# a10 = mysql(sql='insert into text(text_name,text_content)values("%s","%s")' % (name, content))
# print(a10.insert_username_password())

